package DAL.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import DAL.Entites.*;


public class UserJDBC {
	
	Connection conn;

	String tableName = "users"; 

	public UserJDBC(Connection connection) {
		this.conn = connection;
	}
	
	public void insert(String FirstName, String LastName, String Email, String Phone, String Address, String Password) {
		// this is for selecting db incase we do insert first
		// ((DriverManagerDataSource)dataSource).setUrl("jdbc:mysql://localhost:3306/TestData");
		String SQL = String.format("insert into %s values(null,'%s','%s','%s','%s','%s','%s',NOW())", tableName, FirstName, LastName, Email, Phone, Address, Password);
		
		try {
			Statement st = this.conn.createStatement();
			st.execute(SQL);
			st.close();
			System.out.println("Created Record");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return;
	}
	
	private User mapRow(ResultSet rs) throws SQLException {
		User user = new User(rs.getInt("Id"));
		user.setFirstName(rs.getString("FirstName"));
		user.setLastName(rs.getString("LastName"));
		user.setEmail(rs.getString("Email"));
		user.setPhone(rs.getString("Phone"));
		user.setAddress(rs.getString("Address"));
		user.setPassword(rs.getString("Password"));
		System.out.println("mapRow");

		return user;
	}
	
	public List<User> listUsers() {
		List<User> usersList = new ArrayList<User>();
		String SQL = String.format("select * from %s", tableName);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				usersList.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return usersList;
	}
	
	@SuppressWarnings("finally")
	public boolean updateUser(User user)
	{
		boolean updated = false;
		String Query = String.format(
				"update %s set FirstName = '%s', LastName = '%s', Email = '%s', Phone = '%s', Address = '%s', Password = '%s'  where id = %s",
				tableName,
				user.getFirstName(),
				user.getLastName(),
				user.getEmail(),
				user.getPhone(),
				user.getAddress(),
				user.getPassword(),
				Integer.toString(user.getId())
		);
		try {
			Statement st = conn.createStatement();
			st.execute(Query);
			st.close();
			updated = true;
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		finally{
			return updated;
		}
		
	}
	
	@SuppressWarnings("finally")
	public User getUserById(int id) {
		User user = null;
		String SQL = String.format("select * from %s where id = %s", tableName, id);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				user = mapRow(rs);
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		finally{
			return user;
		}
	}
	
	@SuppressWarnings("finally")
	public User getUserByEmail(String Email) {
		User user = null;
		String SQL = String.format("select * from %s where email = '%s'", tableName, Email);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				user = mapRow(rs);
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		finally{
			return user;
		}
	}
	
}
